﻿using System;
using System.Collections;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Data.SqlTypes;
using System.IO;

namespace iResume
{
    public class DBManager
    {
        //static string CONNECTIONSTRING = ConfigurationManager.ConnectionStrings["ewdtjobonlineappConnectionString1"].ToString();
        static string CONNECTIONSTRING = ConfigurationManager.ConnectionStrings["iResumeConnectionString"].ToString();

        //RECRUITER-------------------------------------------------------------------------------------------------------------------------------------------
        //create recruiter-------------------------------------------------------------------------------------------------------------------------------------------
        public static void createRecruiter(Recruiter R)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                comm.Connection = conn;
                string query = "";
                query += " INSERT into Recruiter (companyName, personInCharge, email, password, rePassword)";
                query += " VALUES                (@companyName, @personInCharge, @email, @password, @rePassword)";
                comm.CommandText = query;

                comm.Parameters.AddWithValue("@companyName", R.CompanyName);
                comm.Parameters.AddWithValue("@personInCharge", R.PersonInCharge);
                comm.Parameters.AddWithValue("@email", R.Email);
                comm.Parameters.AddWithValue("@password", R.Password);
                comm.Parameters.AddWithValue("@rePassword", R.RePassword);


                int rowsAdded = (int)comm.ExecuteNonQuery();


            }
            catch(SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        
        //recruiter update profile-------------------------------------------------------------------------------------------------------------------------------------------
        public static bool updateRecruiter(Recruiter update)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";

                query += "UPDATE recruiter SET companyName=@companyName, personInCharge=@personInCharge, ";
                query += "                      companyAddress=@companyAddress, companyURL=@companyURL, companyProfile=@companyProfile, ";
                query +="                       contact=@contact, position=@position, department=@department, logo=@logo, postalCode=@postalCode WHERE email=@email";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@companyName", update.CompanyName);
                comm.Parameters.AddWithValue("@personInCharge", update.PersonInCharge);          
                comm.Parameters.AddWithValue("@companyAddress", update.CompanyAddress);
                comm.Parameters.AddWithValue("@companyURL", update.CompanyURL);
                comm.Parameters.AddWithValue("@companyProfile", update.CompanyProfile);
                comm.Parameters.AddWithValue("@contact", update.ContactNum);
                comm.Parameters.AddWithValue("@position", update.Position);
                comm.Parameters.AddWithValue("@department", update.Department);
                comm.Parameters.AddWithValue("@email", update.Email);
                comm.Parameters.AddWithValue("@postalCode", update.PostalCode);
                SqlParameter pictureParameter = new SqlParameter("@logo", SqlDbType.Image);
                pictureParameter.Value = update.CompanyLogo;
                comm.Parameters.Add(pictureParameter);
                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
    

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        public static bool updateRecruiter1(Recruiter update)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";

                query += "UPDATE recruiter SET companyName=@companyName, personInCharge=@personInCharge, ";
                query += "                      companyAddress=@companyAddress, companyURL=@companyURL, companyProfile=@companyProfile, ";
                query += "                       contact=@contact, position=@position, department=@department, postalCode=@postalCode WHERE email=@email";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@companyName", update.CompanyName);
                comm.Parameters.AddWithValue("@personInCharge", update.PersonInCharge);
                comm.Parameters.AddWithValue("@companyAddress", update.CompanyAddress);
                comm.Parameters.AddWithValue("@companyURL", update.CompanyURL);
                comm.Parameters.AddWithValue("@companyProfile", update.CompanyProfile);
                comm.Parameters.AddWithValue("@contact", update.ContactNum);
                comm.Parameters.AddWithValue("@position", update.Position);
                comm.Parameters.AddWithValue("@department", update.Department);
                comm.Parameters.AddWithValue("@email", update.Email);
                comm.Parameters.AddWithValue("@postalCode", update.PostalCode);
                //SqlParameter pictureParameter = new SqlParameter("@logo", SqlDbType.Image);
                //pictureParameter.Value = update.CompanyLogo;
                //comm.Parameters.Add(pictureParameter);
                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
       
        //recruite Job Post create ----------------------------------------------------------------------------------------------------------------------------
        public static void createJobRecruiter(Recruiter createJobPost)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                comm.Connection = conn;
                string query = "";
                query += " INSERT INTO jobPosting (email, SalaryRange, JobPosition, category, JobDescription, VacancyAvailable)";
                query += " VALUES                 (@email, @SalaryRange, @JobPosition, @category, @JobDescription, @VacancyAvailable)";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@email", createJobPost.Email);
                comm.Parameters.AddWithValue("@SalaryRange", createJobPost.SalaryRange);
                comm.Parameters.AddWithValue("@JobPosition", createJobPost.JobPosition);
                comm.Parameters.AddWithValue("@category", createJobPost.Category);
                comm.Parameters.AddWithValue("@JobDescription", createJobPost.JobDescription);
                comm.Parameters.AddWithValue("@VacancyAvailable", createJobPost.VacancyAvabile);
                int rowsAdded = (int)comm.ExecuteNonQuery();


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }

        }
       
        //recruiter update job Post------------------------------------------------------------------------------------------------------------------------------------------
        public static bool updateJobPostRecruiter(Recruiter updateJob)
        {
            SqlConnection conn = new SqlConnection();
            try
            {

                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";
                query += "UPDATE jobPosting SET SalaryRange=@SalaryRange, ";
                query += "                     JobPosition=@JobPosition, category=@category, JobDescription=@JobDescription, VacancyAvailable=@VacancyAvailable ";
                query += "                     WHERE jobID=@jobID";                                 
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@jobID", updateJob.JobID);
                comm.Parameters.AddWithValue("@SalaryRange", updateJob.SalaryRange);
                comm.Parameters.AddWithValue("@JobPosition", updateJob.JobPosition);
                comm.Parameters.AddWithValue("@category", updateJob.Category);
                comm.Parameters.AddWithValue("@JobDescription", updateJob.JobDescription);
                comm.Parameters.AddWithValue("@VacancyAvailable", updateJob.VacancyAvabile);
                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        
        // recruiter retrieve job Post---------------------------------------------------------------------------------------------------------------------------------------
        public static ArrayList retrieveJobPostRecruiter(string username, string remail)
        {
    
            ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT r.companyName, r.companyAddress, r.companyURL, r.companyProfile, ";
                query += "       r.email, r.personInCharge, r.contact, r.postalCode, jp.jobID, jp.SalaryRange, jp.JobPosition, jp.category, jp.JobDescription, jp.VacancyAvailable ";
                query += "       FROM Recruiter r, jobPosting jp WHERE jp.jobID=@jobID and r.email=@email";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@jobID", username);
                comm.Parameters.AddWithValue("@email", remail);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    
                    string companyName = dr["companyName"].ToString();
                    string companyAddress = dr["companyAddress"].ToString();
                    string companyURL = dr["companyURL"].ToString();
                    string companyProfile = dr["companyProfile"].ToString();
                    string email = dr["email"].ToString();
                    string personInCharge = dr["personInCharge"].ToString();
                    string contactNum = dr["contact"].ToString();
                    string postalCode = dr["postalCode"].ToString();
                    string salaryRange = dr["SalaryRange"].ToString();
                    string jobPosition = dr["JobPosition"].ToString();
                    string category = dr["category"].ToString();
                    string jobDescription = dr["JobDescription"].ToString();
                    string vacancyAvabile = dr["VacancyAvailable"].ToString();
                    string jobID = dr["jobID"].ToString();

                    Recruiter r = new Recruiter(companyName, companyAddress, companyProfile, personInCharge, email, contactNum, companyURL, salaryRange, jobPosition, category, jobDescription, vacancyAvabile, jobID, postalCode);
                    results.Add(r);


                }

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;

        }
       
        //recruiter retrieve profile-------------------------------------------------------------------------------------------------------------------------------------------
        public static ArrayList retrieveRecruiter(string username)
        {
            ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT companyName, personInCharge, email, companyAddress,companyURL, companyProfile, contact, position, department, logo, postalCode FROM Recruiter WHERE email=@email";
     
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@email", username);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                  
                    string companyName = dr["companyName"].ToString();
                    string personInCharge = dr["personInCharge"].ToString();
                    string email = dr["email"].ToString();
                    string companyAddress = dr["companyAddress"].ToString();
                    string companyURL = dr["companyURL"].ToString();
                    string companyProfile = dr["companyProfile"].ToString();
                    string contactNum = dr["contact"].ToString();
                    string position = dr["position"].ToString();
                    string department = dr["department"].ToString();
                    string postalCode = dr["postalCode"].ToString();

                    Recruiter r = new Recruiter(companyName, companyAddress, companyProfile, personInCharge, email, contactNum, companyURL, position, department, null, postalCode);
                    results.Add(r);


                }

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
            
        }
       
        //validate emailAdd
        public static ArrayList retrieveREmail()
        {
             ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query +="SELECT email from Recruiter";
                comm.CommandText = query;
               // comm.Parameters.AddWithValue("@email", username);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string email = dr["email"].ToString();
                    //Recruiter r = new Recruiter(email);
                    results.Add(email);
                }
                  }

            
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
            

        }
       
        //recruiter delete ------------------------------------------------------------------------------------------------------------------------------------------
        public static bool deleteRecruiter(string username)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();

                comm.CommandText = "DELETE FROM Recruiter WHERE email = @email";
                comm.Parameters.AddWithValue("@email", username);
                comm.Connection = conn;
                int rowAffected = comm.ExecuteNonQuery();
                if (rowAffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }
            return false;

            
        }
        
        //recruiter login-------------------------------------------------------------------------------------------------------------------------------------------
        public static bool recruiterlogin(string rcemail, string rcpassword)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT email,password FROM Recruiter WHERE (email = @email) And(password = @password)";
                comm.Parameters.AddWithValue("@email", rcemail);
                comm.Parameters.AddWithValue("@password", rcpassword);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

                //if (dr.Read() == true)
                //{
                //    //if has row - means user is real 
                //    result = true;
                //}
                //else
                //{
                //    //if no row - means user is bluff
                //    result = false;
                //}
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //recruiter display Image ----------------------------------------------------------------------------------------------------------------------------------
        public static Stream DisplayImage(string email)
        {

            ArrayList imgList = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            byte[] binary = new byte[0];
            //byte[] binData;

            try
            {

                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT logo from dbo.Recruiter where email = @email";
                comm.Parameters.AddWithValue("@email", email);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string content = dr["logo"].ToString();
                    //binary = (byte[])dr["logo"];
                    if (dr["logo"].Equals(DBNull.Value))
                    {
                        binary = System.Text.Encoding.UTF8.GetBytes(String.Empty);
                    }
                    else
                    {
                        binary = (byte[])dr["logo"];
                        imgList.Add(content);
                        imgList.Add(binary);
                    }
                    //string content = dr["logo"].ToString();
                    //binary = (byte[])dr["logo"];
                    //imgList.Add(content);
                    //imgList.Add(binary);

                }
                return new MemoryStream((byte[])binary);

            }
            catch (SqlException ex)
            {
                string msg = ex.Message;
                throw new Exception();
            }
            finally
            {
                conn.Close();
            }
        }

        //recruiter reset password ----------------------------------------------------------------------------------------------------------------------------------
        public static bool rResetpassword(string rpassword, string remail)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "UPDATE Recruiter SET password=@rpass WHERE email= @mail";
                comm.Parameters.AddWithValue("@rpass", rpassword);
                comm.Parameters.AddWithValue("@mail", remail);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

                //if (dr.Read() == true)
                //{
                //    //if has row - means user is real 
                //    result = true;
                //}
                //else
                //{
                //    //if no row - means user is bluff
                //    result = false;
                //}
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
  
        //recruiter forgetpassword ----------------------------------------------------------------------------------------------------------------------------------
        public static bool rforgetpassword(string email)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT email FROM recruiter WHERE (email = @mail)";
                comm.Parameters.AddWithValue("@mail", email);

                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //recruiter confirmationcode ----------------------------------------------------------------------------------------------------------------------------------  

        public static bool rconfirmcode(string code)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT confirmcode FROM Recruiter WHERE (confirmcode = @code)";
                comm.Parameters.AddWithValue("@code", code);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

                //if (dr.Read() == true)
                //{
                //    //if has row - means user is real 
                //    result = true;
                //}
                //else
                //{
                //    //if no row - means user is bluff
                //    result = false;
                //}
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        public static bool rupdateconfirmcode(Recruiter update)
        {
            SqlConnection conn = new SqlConnection();
            try
            {

                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";

                query += "UPDATE Recruiter SET confirmcode=@code WHERE email=@email ";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@email", update.Email);
                comm.Parameters.AddWithValue("@code", update.ConfirmationCode);

                
                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }


        //JOBSEEKER-------------------------------------------------------------------------------------------------------------------------------------------
       //validate email
        public static ArrayList retrieveJEmail()
        {
            ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT emailAdd from JobSeeker";
                comm.CommandText = query;
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string email = dr["emailAdd"].ToString();
                    results.Add(email);
                }
            }


            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;


        }
        //jobseekerviewjobpost
        public static ArrayList retrieveJobPostJobSeeker(string username)
        {
            ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT r.companyName, r.companyAddress, r.companyURL, r.companyProfile, ";
                query += "       r.email, r.personInCharge, r.contact, r.postalCode, jp.jobID, jp.SalaryRange, jp.JobPosition, jp.category, jp.JobDescription, jp.VacancyAvailable ";
                query += "       FROM Recruiter r, jobPosting jp WHERE jp.jobID=@jobID and r.email=jp.email";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@jobID", username);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {

                    string companyName = dr["companyName"].ToString();
                    string companyAddress = dr["companyAddress"].ToString();
                    string companyURL = dr["companyURL"].ToString();
                    string companyProfile = dr["companyProfile"].ToString();
                    string email = dr["email"].ToString();
                    string personInCharge = dr["personInCharge"].ToString();
                    string contactNum = dr["contact"].ToString();
                    string postalCode = dr["postalCode"].ToString();
                    string salaryRange = dr["SalaryRange"].ToString();
                    string jobPosition = dr["JobPosition"].ToString();
                    string category = dr["category"].ToString();
                    string jobDescription = dr["JobDescription"].ToString();
                    string vacancyAvabile = dr["VacancyAvailable"].ToString();
                    string jobID = dr["jobID"].ToString();


                    Recruiter r = new Recruiter(companyName, companyAddress, companyProfile, personInCharge, email, contactNum, companyURL, salaryRange, jobPosition, category, jobDescription, vacancyAvabile, jobID, postalCode);
                    results.Add(r);


                }

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;
        }
        
        //jobseeker resetpassword ----------------------------------------------------------------------------------------------------------------------------------

        public static bool jsResetpassword(string jspassword, string jsemail)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "UPDATE Jobseeker SET password=@jspass WHERE emailAdd = @mail";
                comm.Parameters.AddWithValue("@jspass", jspassword);
                comm.Parameters.AddWithValue("@mail", jsemail);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

                //if (dr.Read() == true)
                //{
                //    //if has row - means user is real 
                //    result = true;
                //}
                //else
                //{
                //    //if no row - means user is bluff
                //    result = false;
                //}
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //Create JobSeeker-------------------------------------------------------------------------------------------------------------------------------------------
        public static void createJobSeeker(JobSeeker J)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                comm.Connection = conn;
                string query = "";
                query += " INSERT into JobSeeker (fullName, emailAdd,password,category)";
                query += " VALUES                (@fullName, @emailAdd, @password, @category)";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@fullName", J.FullName);
                comm.Parameters.AddWithValue("@emailAdd", J.EmailAdd);
                comm.Parameters.AddWithValue("@password", J.Password);
                comm.Parameters.AddWithValue("@category", J.Jobcategory);

                int rowsAdded = (int)comm.ExecuteNonQuery();
                    

            }
            catch(SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        //JobSeeker login-------------------------------------------------------------------------------------------------------------------------------------------
        public static bool jobseekerlogin(string jsemail, string jspassword)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT emailAdd,password FROM JobSeeker WHERE (emailAdd = @email) And(password = @password)";
                comm.Parameters.AddWithValue("@email", jsemail);
                comm.Parameters.AddWithValue("@password", jspassword);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

              
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //JobSeeker update resume-------------------------------------------------------------------------------------------------------------------------------------------
        public static bool updateJsResume(JobSeeker updatejs)
        {
            SqlConnection conn = new SqlConnection();
            try
            {

                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";

                query += "UPDATE JobSeeker SET category=@category, profile=@profile, ";
                query += "                      education=@education, Skills=@Skills, workexperience=@workexperience, profilepic=@pro, fullname=@fn, Name=@Name, data=@data WHERE emailAdd=@email ";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@category", updatejs.Jobcategory);
                comm.Parameters.AddWithValue("@profile", updatejs.Profile);
                comm.Parameters.AddWithValue("@education", updatejs.Education);
                comm.Parameters.AddWithValue("@skills", updatejs.Skills);
                comm.Parameters.AddWithValue("@workexperience", updatejs.Workexperience);
                comm.Parameters.AddWithValue("@email", updatejs.EmailAdd);
                comm.Parameters.AddWithValue("@fn", updatejs.FullName);
                comm.Parameters.AddWithValue("@Name", updatejs.Name);
                comm.Parameters.AddWithValue("@data", updatejs.Data);
                SqlParameter pictureParameter = new SqlParameter("@pro", SqlDbType.Image);

                //pictureParameter.Value = content;
                pictureParameter.Value = updatejs.Image;
                comm.Parameters.Add(pictureParameter);
                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        
        public static bool updateJsResume1(JobSeeker updatejs)
        {
            SqlConnection conn = new SqlConnection();
            try
            {

                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";

                query += "UPDATE JobSeeker SET category=@category, profile=@profile, ";
                query += "                      education=@education, Skills=@Skills, workexperience=@workexperience, fullname=@fn, Name=@Name, data=@data WHERE emailAdd=@email ";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@category", updatejs.Jobcategory);
                comm.Parameters.AddWithValue("@profile", updatejs.Profile);
                comm.Parameters.AddWithValue("@education", updatejs.Education);
                comm.Parameters.AddWithValue("@skills", updatejs.Skills);
                comm.Parameters.AddWithValue("@workexperience", updatejs.Workexperience);
                comm.Parameters.AddWithValue("@email", updatejs.EmailAdd);
                comm.Parameters.AddWithValue("@fn", updatejs.FullName);
                comm.Parameters.AddWithValue("@Name", updatejs.Name);
                comm.Parameters.AddWithValue("@data", updatejs.Data);
                //SqlParameter pictureParameter = new SqlParameter("@pro", SqlDbType.Image);

                ////pictureParameter.Value = content;
                //pictureParameter.Value = updatejs.Image;
                //comm.Parameters.Add(pictureParameter);
                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        //JobSeeker retrieve resume-------------------------------------------------------------------------------------------------------------------------------------------

        public static ArrayList retrievejs(string username)
        {
            ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT category,emailAdd, profile, education, skills,workexperience,profilepic,fullname FROM JobSeeker WHERE emailAdd=@email";
                //   query += "        companyURl, companyProfile, contact, position, department, logo)";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@email", username);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    //string nric = dr["nric"].ToString();
                    string category = dr["category"].ToString();
                    string profile = dr["profile"].ToString();
                    string education = dr["education"].ToString();
                    string skills = dr["skills"].ToString();
                    string workexperience = dr["workexperience"].ToString(); 
                    string email = dr["emailAdd"].ToString();
                    string fullname = dr["fullname"].ToString();
     
                

                    JobSeeker js = new JobSeeker(category,profile,education,skills,workexperience,email,fullname,null,null,null);
                    results.Add(js);


                }

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            
            return results;
            

        }
        //JobSeeker update contact-------------------------------------------------------------------------------------------------------------------------------------------
        public static bool updateJscontact(JobSeeker updatejs)
        {
            SqlConnection conn = new SqlConnection();
            try
            {

                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";

                query += "UPDATE JobSeeker SET location=@loc,contactemail=@contacte, phone=@phone WHERE emailAdd=@email ";
        
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@loc", updatejs.Location);
                comm.Parameters.AddWithValue("@contacte", updatejs.Contactemail);
                comm.Parameters.AddWithValue("@phone", updatejs.Phone);
                comm.Parameters.AddWithValue("@email", updatejs.EmailAdd);
         

                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }



        //JobSeeker retrieve contact info-------------------------------------------------------------------------------------------------------------------------------------------

        public static ArrayList retrievejscontact(string email)
        {
            ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT location,contactemail, phone FROM JobSeeker WHERE emailAdd=@email";
                //   query += "        companyURl, companyProfile, contact, position, department, logo)";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@email", email);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    //string nric = dr["nric"].ToString();
                    string loc = dr["location"].ToString();
                    string contact = dr["contactemail"].ToString();
                    string phone = dr["phone"].ToString();
          


                    JobSeeker js = new JobSeeker(loc,contact,phone,email);
                    results.Add(js);


                }

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;

        }

        //Jobseeker display image-------------------------------------------------------------------------------------------------------------------------------------------
        public static Stream DisplayImagejs(string email)
        {

            ArrayList imgList = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            byte[] binary = new byte[0];


            try
            {

                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT profilepic from JobSeeker where emailAdd = @email";
                comm.Parameters.AddWithValue("@email",email);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string content = dr["profilepic"].ToString();
                    //binary = (byte[])dr["logo"];
                    if (dr["profilepic"].Equals(DBNull.Value))
                    {
                        binary = System.Text.Encoding.UTF8.GetBytes(String.Empty);
                    }
                    else
                    {

                        binary = (byte[])dr["profilepic"];
                        imgList.Add(content);
                        imgList.Add(binary);
                    }

                }
                return new MemoryStream((byte[])binary);

            }
            catch (SqlException)
            {
                throw new Exception();
            }
            finally
            {
                conn.Close();
            }
        }
 
        //deleteJobSeeker------------------------------------------------------------------------------------------------------------------------------------
        public static bool deleteJobseeker(string username)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();

                comm.CommandText = "DELETE FROM JobSeeker WHERE emailAdd = @emailAdd";
                comm.Parameters.AddWithValue("@emailAdd", username);
                comm.Connection = conn;
                int rowAffected = comm.ExecuteNonQuery();
                if (rowAffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }
            return false;


        }

     //jobseeker confirmationcode ----------------------------------------------------------------------------------------------------------------------------------  
        public static bool jsupdateconfirmcode(JobSeeker update)
        {
            SqlConnection conn = new SqlConnection();
            try
            {

                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";

                query += "UPDATE JobSeeker SET confirmationcode=@code WHERE emailAdd=@email ";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@code", update.Confirmationcode);
         
                comm.Parameters.AddWithValue("@email", update.EmailAdd);
                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }


   //jobseeker confirmationcode ----------------------------------------------------------------------------------------------------------------------------------  
   
        public static bool jsconfirmcode(string code)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT confirmationcode FROM JobSeeker WHERE (confirmationcode = @code)";
                comm.Parameters.AddWithValue("@code", code);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

                //if (dr.Read() == true)
                //{
                //    //if has row - means user is real 
                //    result = true;
                //}
                //else
                //{
                //    //if no row - means user is bluff
                //    result = false;
                //}
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }


        //jobseeker forgetpassword ----------------------------------------------------------------------------------------------------------------------------------  
        public static bool jsforgetpassword(string email)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT emailAdd FROM jobseeker WHERE (emailAdd = @mail)";
                comm.Parameters.AddWithValue("@mail", email);

                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        
        
        //ADMIN-------------------------------------------------------------------------------------------------------------------------------------------
        //Create Admin-------------------------------------------------------------------------------------------------------------------------------------------
        public static ArrayList retrieveAEmail()
        {
            ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT email from Admin";
                comm.CommandText = query;
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string email = dr["email"].ToString();
                    results.Add(email);
                }
            }


            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;


        }
        public static void createAdmin(Admin A)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = CONNECTIONSTRING;
                string query = "";
                query += "INSERT INTO Admin (fullname, email, password, dateofbirth, ";
                query += "                         gender, address, postalcode,nationality, contact) ";
                query += "VALUES                  (@fullname, @email, @password, @dateofbirth,  ";
                query += "                         @gender, @address, @postalcode,@nationality, @contact) ";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@fullname", A.Email);
                comm.Parameters.AddWithValue("@email", A.Fullname);
                comm.Parameters.AddWithValue("@password", A.Password);
                comm.Parameters.AddWithValue("@dateofbirth", A.Dateofbirth);
                comm.Parameters.AddWithValue("@gender", A.Gender);
                comm.Parameters.AddWithValue("@address", A.Address);
                comm.Parameters.AddWithValue("@postalcode", A.Postalcode);
                comm.Parameters.AddWithValue("@nationality", A.Nationality);
                comm.Parameters.AddWithValue("@contact", A.Contact);


                int rowsAdded = (int)comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        //Admin login-------------------------------------------------------------------------------------------------------------------------------------------
        public static bool adminlogin(string ademail, string adpassword)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT email,password FROM Admin WHERE (email = @email) And(password = @password)";
                comm.Parameters.AddWithValue("@email", ademail);
                comm.Parameters.AddWithValue("@password", adpassword);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

                //if (dr.Read() == true)
                //{
                //    //if has row - means user is real 
                //    result = true;
                //}
                //else
                //{
                //    //if no row - means user is bluff
                //    result = false;
                //}
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //Admin Display img-------------------------------------------------------------------------------------------------------------------------------------------
      

        public static Stream DisplayImageadmin(string email)
        {

            ArrayList imgList = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            byte[] binary = new byte[0];


            try
            {

                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT pic from Admin where email = @email";
                comm.Parameters.AddWithValue("@email", email);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string content = dr["pic"].ToString();
                    if (dr["pic"].Equals(DBNull.Value))
                    {
                        binary = System.Text.Encoding.UTF8.GetBytes(String.Empty);
                    }
                    else
                    {
                        binary = (byte[])dr["pic"];
                        imgList.Add(content);
                        imgList.Add(binary);
                    }

                }
                return new MemoryStream((byte[])binary);

            }
            catch (SqlException ex)
            {
                string msg = ex.Message;
                throw new Exception();
            }
            finally
            {
                conn.Close();
            }
        }
        //deleteAdmin----------------------------------------------------------------------------------------------------------------------------------
        public static bool deleteAdmin(string username)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();

                comm.CommandText = "DELETE FROM Admin WHERE email= @email";
                comm.Parameters.AddWithValue("@email", username);
                comm.Connection = conn;
                int rowAffected = comm.ExecuteNonQuery();
                if (rowAffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }

            }
            catch
            {

            }
            finally
            {
                conn.Close();
            }
            return false;


        }

        //retrieveAdmin----------------------------------------------------------------------------------------------------------------------------------
        public static ArrayList retrievead(string username)
        {
            ArrayList results = new ArrayList();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT fullname, email, dateofbirth, gender,address,postalcode,nationality,contact,pic FROM admin WHERE email=@email";
                //   query += "        companyURl, companyProfile, contact, position, department, logo)";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@email", username);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    //string nric = dr["nric"].ToString();
                    string fullname = dr["fullname"].ToString();
                    string email = dr["email"].ToString();
                    DateTime dateofbirth = dr.GetDateTime(2);
                    string gender = dr["gender"].ToString();
                    string address = dr["address"].ToString();
                    string postalcode = dr["postalcode"].ToString();
                    string nationality = dr["nationality"].ToString();
                    string contact = dr["contact"].ToString();





                    Admin ad = new Admin(fullname, email, dateofbirth, gender, address, postalcode, nationality,contact,null);
                    results.Add(ad);


                }

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;

        }

        //UpdateAdmin----------------------------------------------------------------------------------------------------------------------------------
        public static bool updateadministrator(Admin update)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";
                query += "UPDATE Admin SET fullname=@fullname, dateofbirth=@dateofbirth, gender=@gender, address=@address,";
                query += "                 postalcode=@postalcode, nationality=@nationality,contact=@con, pic=@p where email=@email";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@fullname", update.Fullname);
                comm.Parameters.AddWithValue("@email", update.Email);
                comm.Parameters.AddWithValue("@dateofbirth", update.Dateofbirth);
                comm.Parameters.AddWithValue("@gender", update.Gender);
                comm.Parameters.AddWithValue("@address", update.Address);
                comm.Parameters.AddWithValue("@postalcode", update.Postalcode);
                comm.Parameters.AddWithValue("@nationality", update.Nationality);
                comm.Parameters.AddWithValue("@con", update.Contact);
                SqlParameter pictureParameter = new SqlParameter("@p", SqlDbType.Image);

                //pictureParameter.Value = content;
                pictureParameter.Value = update.Image;
                comm.Parameters.Add(pictureParameter);
                comm.Connection = conn;


                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }
        public static bool updateadministrator1(Admin update)
        {
            SqlConnection conn = new SqlConnection();
            try
            {
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";
                query += "UPDATE Admin SET fullname=@fullname, dateofbirth=@dateofbirth, gender=@gender, address=@address,";
                query += "                 postalcode=@postalcode, nationality=@nationality,contact=@con where email=@email";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@fullname", update.Fullname);
                comm.Parameters.AddWithValue("@email", update.Email);
                comm.Parameters.AddWithValue("@dateofbirth", update.Dateofbirth);
                comm.Parameters.AddWithValue("@gender", update.Gender);
                comm.Parameters.AddWithValue("@address", update.Address);
                comm.Parameters.AddWithValue("@postalcode", update.Postalcode);
                comm.Parameters.AddWithValue("@nationality", update.Nationality);
                comm.Parameters.AddWithValue("@con", update.Contact);
               
                comm.Connection = conn;


                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        //admin forgetpassword ----------------------------------------------------------------------------------------------------------------------------------  
        public static bool adminforgetpassword(string email)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT email FROM Admin WHERE (email = @mail)";
                comm.Parameters.AddWithValue("@mail", email);

                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        //admin reset password ----------------------------------------------------------------------------------------------------------------------------------
        public static bool aResetpassword(string apassword, string aemail)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "UPDATE Admin SET password=@apass WHERE email= @mail";
                comm.Parameters.AddWithValue("@apass", apassword);
                comm.Parameters.AddWithValue("@mail", aemail);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

                //if (dr.Read() == true)
                //{
                //    //if has row - means user is real 
                //    result = true;
                //}
                //else
                //{
                //    //if no row - means user is bluff
                //    result = false;
                //}
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }
        //admin confirmationcode ----------------------------------------------------------------------------------------------------------------------------------  

        public static bool aconfirmcode(string code)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;

            try
            {
                //step 1: Connect to database
                conn.Open();

                //step 2: Pepare the SQL command
                SqlCommand comm = new SqlCommand();
                comm.CommandText = "SELECT confirmcode FROM Admin WHERE (confirmcode = @code)";
                comm.Parameters.AddWithValue("@code", code);
                comm.Connection = conn;

                //step 3: Execute the SQL command
                //select = ExecuteReader
                //insert,delete, update = ExecuteNonQuery
                SqlDataReader dr = comm.ExecuteReader();

                result = dr.Read();

                //if (dr.Read() == true)
                //{
                //    //if has row - means user is real 
                //    result = true;
                //}
                //else
                //{
                //    //if no row - means user is bluff
                //    result = false;
                //}
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static bool aupdateconfirmcode(Admin update)
        {
            SqlConnection conn = new SqlConnection();
            try
            {

                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = CONNECTIONSTRING;
                conn.Open();
                string query = "";

                query += "UPDATE Admin SET confirmcode=@code WHERE email=@email ";
                comm.CommandText = query;
                comm.Parameters.AddWithValue("@email", update.Email);
                comm.Parameters.AddWithValue("@code", update.ConfirmationCode);

                
                comm.Connection = conn;
                int rowsUpdated = (int)comm.ExecuteNonQuery();

                if (rowsUpdated > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }


            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return false;
        }

        //count resume--------------------

        public static string countresume()
        {
            string results = "";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {
                 
                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT COUNT(*) AS total FROM JobSeeker";
                comm.CommandText = query;
                //comm.Parameters.AddWithValue("@email", email);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read()) 
                {
                    results = dr["total"].ToString();
                }

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;

        }

        //count recruiter--------------------

        public static string countrecruiter()
        {
            string results = "";
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = CONNECTIONSTRING;
            try
            {

                conn.Open();
                SqlCommand comm = new SqlCommand();
                string query = "";
                query += "SELECT COUNT(*) AS total FROM Recruiter";
                comm.CommandText = query;
                //comm.Parameters.AddWithValue("@email", email);
                comm.Connection = conn;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    results = dr["total"].ToString();
                }

            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
            return results;

        }


    }




}